﻿using HCQ2_Common;
using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model.VLine.InputDto;
using HCQ2_Model.VLine.OutputDto;
using HCQ2_Model.VLine.Params;
using HCQ2_Model.VLine.Result;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VLinesExperienceDetailDAL: IT_VLinesExperienceDetailDAL
    {
        //*********************************后端**************************************
        /// <summary>
        ///  获取成绩主表列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<ScoreStatistList> GetScoreStatistList(ScoreStatistParam model,out int total)
        {
            sb?.Clear();
            sb.Append(GetScoreStatistListSql(model));
            total= Helper.ToInt(SqlHelper.ExecuteScalar("SELECT COUNT(0) AS num FROM " + sb.ToString()));
            DataTable dt = SqlHelper.ExecuteDataTable($"SELECT TOP {model.rows} * FROM " +sb.ToString()+$" WHERE k.rowNum>{(model.page - 1) * model.rows} ");
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<ScoreStatistList>(dt);
        }
        /// <summary>
        ///  获取成绩主表导出数据table
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public DataTable GetScoreStatistExeclData(ScoreStatistParam model)
        {
            sb?.Clear();
            sb.Append("SELECT * FROM ");
            sb.Append(GetScoreStatistListSql(model));
            return SqlHelper.ExecuteDataTable(sb.ToString());
        }
        private string GetScoreStatistListSql(ScoreStatistParam model)
        {
            StringBuilder _sb = new StringBuilder();
            _sb.Append(@" (SELECT rowNum=CAST((ROW_NUMBER() OVER(ORDER BY n.create_date DESC)) AS int),m.*,n.* FROM 
            (SELECT file_code,file_id,file_name=(SELECT file_name FROM dbo.T_DocumentInfo info WHERE info.file_id=main.file_id),
            folder_code,num,accuracy=CONVERT(decimal(18,2),(CONVERT(decimal(18,2),result_ok)/CONVERT(decimal(18,2),result_ok+result_error))*100),result_ok,result_error,
            content,answer,parse FROM T_VRExperienceWorkMain main WHERE 1=1 ");
            if (!string.IsNullOrEmpty(model.file_code))
                _sb.Append($" AND file_code='{model.file_code}' ");
            _sb.Append($@")m inner join (select file_code from T_DocumentInfo where file_status=5 and if_remove=0)info on m.file_code=info.file_code LEFT JOIN (SELECT room.*,files.FileId FROM (SELECT RmId, CrName AS cr_name, UserName AS user_name, 
            CONVERT(varchar(100), CreateDate, 120) AS create_date FROM dbo.N_VRLineClassRoom WHERE 1 = 1 ");
            if (!string.IsNullOrEmpty(model.user_name))
                _sb.Append($" AND UserName='{model.user_name}' ");
            if (!string.IsNullOrEmpty(model.start_date) && !string.IsNullOrEmpty(model.end_date))
                _sb.AppendFormat($@" AND CreateDate>='{model.start_date}' AND CreateDate<='{model.end_date}' ");
            else if (!string.IsNullOrEmpty(model.start_date) && string.IsNullOrEmpty(model.end_date))
                _sb.AppendFormat($@" AND CreateDate>='{model.start_date}' ");
            else if (string.IsNullOrEmpty(model.start_date) && !string.IsNullOrEmpty(model.end_date))
                _sb.AppendFormat($@" AND CreateDate<='{model.end_date}' ");
            _sb.Append($@")room INNER JOIN(SELECT RmId,FileId FROM dbo.N_VRLineClassRoomFile) files ON files.RmId = room.RmId)n
            ON m.file_id=n.FileId)k ");
            return _sb.ToString();
        }
        /// <summary>
        ///  获取成绩明细列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<ScoreStatistDetial> GetScoreStatistDetial(ScoreStatistDetail model, out int total)
        {
            sb?.Clear();
            sb.Append(GetScoreStatistDetialSql(model));            
            total= Helper.ToInt(SqlHelper.ExecuteScalar("SELECT COUNT(0) AS num FROM " + sb.ToString()));
            DataTable dt = SqlHelper.ExecuteDataTable($"SELECT TOP {model.rows} * FROM " + sb.ToString() + $" WHERE k.rowNum>{(model.page - 1) * model.rows}  ORDER BY k.create_date DESC");
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<ScoreStatistDetial>(dt);
        }
        /// <summary>
        ///  获取成绩明细导出数据table
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public DataTable GetScoreStatistDetialExecl(ScoreStatistDetail model)
        {
            sb?.Clear();
            sb.Append("SELECT * FROM ");
            sb.Append(GetScoreStatistDetialSql(model));
            return SqlHelper.ExecuteDataTable(sb.ToString());
        }
        private string GetScoreStatistDetialSql(ScoreStatistDetail model)
        {
            StringBuilder _sb = new StringBuilder();
            _sb.Append($@"(SELECT rowNum=CAST((ROW_NUMBER() OVER(ORDER BY workMain.file_code,detial.score DESC)) AS int),workMain.*,info.file_name,
            u.user_name,rooms.*,detial.user_id,detial.status,detial.score,detial.em_time,detial.sel_result,detial.create_date FROM 
            (SELECT wm_id,line_id,teacher_id,file_id,file_code,folder_code,num,wm_time,content,answer,parse FROM dbo.T_VRExperienceWorkMain WHERE 1=1");
            if (!string.IsNullOrEmpty(model.file_code))
                _sb.Append($" AND file_code='{model.file_code}' ");
            if (!string.IsNullOrEmpty(model.content))
                _sb.Append($" AND content LIKE '{model.content}%' ");
            _sb.Append($@" )workMain INNER JOIN (SELECT file_name,file_id FROM dbo.T_DocumentInfo WHERE if_remove=0");
            if (!string.IsNullOrEmpty(model.file_code))
                _sb.Append($" AND file_code='{model.file_code}' ");
            if (!string.IsNullOrEmpty(model.file_name))
                _sb.Append($" AND file_name='{model.file_name}' ");
            _sb.Append($@")info ON info.file_id = workMain.file_id INNER JOIN
            (SELECT wm_id,user_id,status,score,em_time,sel_result,CONVERT(varchar(100),create_date,120) AS create_date FROM dbo.T_VLinesExperienceDetail)detial ON workMain.wm_id=detial.wm_id 
            INNER JOIN (SELECT user_id,user_name FROM dbo.T_User WHERE 1=1 ");
            if (!string.IsNullOrEmpty(model.user_name))
                _sb.Append($" AND user_name='{model.user_name}' ");
            _sb.Append($@")u ON u.user_id=detial.user_id LEFT JOIN (select  RmId,CrName AS cr_name,UserName AS teacher_name from dbo.N_VRLineClassRoom where 1=1  ");
            if (!string.IsNullOrEmpty(model.start_date) && !string.IsNullOrEmpty(model.end_date))
                _sb.AppendFormat($@" AND CreateDate>='{model.start_date}' AND CreateDate<='{model.end_date}' ");
            else if (!string.IsNullOrEmpty(model.start_date) && string.IsNullOrEmpty(model.end_date))
                _sb.AppendFormat($@" AND CreateDate>='{model.start_date}' ");
            else if (string.IsNullOrEmpty(model.start_date) && !string.IsNullOrEmpty(model.end_date))
                _sb.AppendFormat($@" AND CreateDate<='{model.end_date}' ");
            _sb.Append(")rooms on rooms.RmId=workMain.line_id)k");
            return _sb.ToString();
        }

        //*********************************接口**************************************
        /// <summary>
        ///  获取成绩主表记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<ExperScoreMain> GetExperScoreMainList(ExperLineParam model)
        {
            sb?.Clear();
            sb.Append($@"SELECT r.*,d.in_date,d.out_date FROM (SELECT m.user_id,m.user_name,m.folder_name,m.file_id,m.file_name,m.file_code,m.file_note,SUM(m.em_time)AS em_time,
            SUM(m.score)AS score,SUM(m.okNum)AS okNum,SUM(m.errorNum)AS errorNum,accuracy=0.0,MAX(m.create_date) AS create_date FROM ( 
            SELECT users.*,info.file_name,info.file_id,f.folder_name,info.file_code,info.file_note,detail.em_time,detail.score,detail.errorNum,detail.okNum,detail.create_date FROM 
             (SELECT file_id,file_name,file_code,file_note FROM dbo.T_DocumentInfo WHERE 1=1 ");
            if (model.file_id > 0)
                sb.Append($" AND  file_id={model.file_id} ");
            sb.Append(@") info INNER JOIN 
             (SELECT file_id, folder_id FROM dbo.T_DocumentFolderRelation)r ON info.file_id = r.file_id INNER JOIN
             (SELECT folder_id, folder_name FROM dbo.T_DocumentFolder)f ON r.folder_id = f.folder_id INNER JOIN  
             (SELECT wm_id,file_id FROM dbo.T_VRExperienceWorkMain WHERE 1=1 ");
            if (model.file_id > 0)
                sb.Append($" AND  file_id={model.file_id} ");
            if (!string.IsNullOrEmpty(model.folder_code))
                sb.Append($" AND folder_code='{model.folder_code}' ");
            if(model.line_id>0)
                sb.Append($" AND line_id={model.line_id} ");
            sb.Append($@" )workMain ON workMain.file_id = info.file_id INNER JOIN
             (SELECT wm_id,user_id,SUM(CASE WHEN status=0 THEN 1 ELSE 0 END)AS errorNum,SUM(status)AS okNum,SUM(score) AS score,SUM(em_time) AS em_time,CONVERT(varchar(100), MAX(create_date),120) AS create_date FROM dbo.T_VLinesExperienceDetail WHERE 1=1 ");
            if (model.user_id > 0)
                sb.Append($" AND user_id={model.user_id} ");
            sb.Append($@" GROUP BY user_id,wm_id) detail ON workMain.wm_id=detail.wm_id INNER JOIN
              (SELECT user_id,user_name FROM dbo.T_User)users ON detail.user_id=users.user_id )m GROUP BY m.folder_name,m.user_id,m.user_name,m.file_id,m.file_name,m.file_code,m.file_note) r INNER JOIN (SELECT file_id,user_id,CONVERT(varchar(100),in_date,120) AS in_date,CONVERT(varchar(100),out_date,120) AS out_date FROM dbo.T_VLionsRecord) d ON r.file_id=d.file_id AND r.user_id=d.user_id order by d.in_date desc ");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<ExperScoreMain>(dt);
        }
        /// <summary>
        ///  获取成绩详细记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<ExperScoreDetial> GetExperScoreDetialList(ExperLineParam model)
        {
            sb?.Clear();
            sb.Append($@"SELECT * FROM (SELECT wm_id,file_id,folder_code,folder_name,num,content,answer,parse,wm_time FROM dbo.T_VRExperienceWorkMain WHERE 1=1 ");
            if (model.file_id > 0)
                sb.Append($" AND  file_id={model.file_id}");
            if (!string.IsNullOrEmpty(model.folder_code))
                sb.Append($" AND folder_code='{model.folder_code}' ");
            if (model.line_id > 0)
                sb.Append($" AND line_id={model.line_id} ");
            sb.Append($@")workMain RIGHT JOIN (SELECT wm_id,user_id,sel_result,status,score,em_time,CONVERT(varchar(100),create_date,120) AS create_date FROM dbo.T_VLinesExperienceDetail WHERE 1=1" );
            if (model.user_id > 0)
                sb.Append($" AND user_id={model.user_id} ");
            sb.Append(" )detial ON workMain.wm_id = detial.wm_id;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<ExperScoreDetial>(dt);
        }
        /// <summary>
        ///  获取成绩统计
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<ExperScoreStatist> GetExperScoreStatist(ExperLineParam model)
        {
            sb.Clear();
            sb.Append($@" SELECT folder_code,folder_name,num,content,answer,parse,result_ok,result_error,
            accuracy=CONVERT(decimal(18,2),(CONVERT(decimal(18,2),result_ok)/CONVERT(decimal(18,2),result_ok+result_error))*100)  FROM dbo.T_VRExperienceWorkMain WHERE file_id={model.file_id} ");
            if (!string.IsNullOrEmpty(model.folder_code))
                sb.Append($" AND folder_code='{model.folder_code}' ");
            if (model.line_id > 0)
                sb.Append($" AND line_id={model.line_id} ");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<ExperScoreStatist>(dt);
        }
        /// <summary>
        ///  获取作品是否有人学习
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public int GetLearnNumByFileId(int file_id, int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0)AS num FROM  T_VLinesExperienceDetail WHERE user_id={user_id} AND wm_id IN(SELECT wm_id FROM T_VRExperienceWorkMain WHERE file_id={file_id})");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  导航树path统计学习人数
        /// </summary>
        /// <param name="folderPath"></param>
        /// <returns></returns>
        public int CountLearnNumByPath(string folderPath)
        {
            sb?.Clear();
            sb.Append($@"SELECT ISNULL(SUM(ISNULL(learn_num,0)),0) AS num FROM dbo.T_DocumentFolder WHERE folder_path LIKE '{folderPath}%';");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
    }
}